﻿Public Class frmMaximosMinimos

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Close()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oFD As New OpenFileDialog
        With oFD
            .Title = "Seleccionar fichero"
            .Filter = "Ficheros de texto (*.xls;*.xlsx)|*.xls;*.xlsx" & _
                      "|Todos los ficheros (*.*)|*.*"
            .FileName = Me.txtRuta.Text
            If .ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Me.txtRuta.Text = .FileName
            End If
        End With
        Excel()
        MsgBox("Ahora puede importar el archivo", MsgBoxStyle.Information, "Aviso")
    End Sub
    Private Sub Excel()

        dgDatos.Rows.Clear()
        Application.DoEvents()

        Dim xl As Object
        Dim wb As Object

        'Crear objeto excel
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        wb = xl.activeworkbook
        wb = xl.Workbooks.Open _
        (txtRuta.Text, True, True, , "")

        Dim rng As Integer = 1
        Dim col As Integer = 1
        Dim i As Integer = 11

        Dim CodigoProducto As String
        Dim Maximo As Double
        Dim Minimi As Double
        Dim CodigoAlmacen As String

        For i = 11 To 3802
            CodigoProducto = wb.sheets(1).cells(i, 2).value
            Maximo = wb.sheets(1).cells(i, 4).value
            Minimi = wb.sheets(1).cells(i, 5).value
            CodigoAlmacen = wb.sheets(1).cells(i, 6).value

            dgDatos.Rows.Add(CodigoProducto, CodigoAlmacen, Minimi, Maximo)

            Application.DoEvents()
        Next

        'cerramos el archivo Excel
        wb.Close(SaveChanges:=False)
        xl.Quit()
    End Sub

    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Dim IDProducto As Integer
        Dim cIDAlmacen As Integer
        Dim Maximo As Integer
        Dim Minimo As Integer
        Dim Zona As String
        Dim Pasillo As String
        Dim Anaquel As String
        Dim Repisa As String
        Dim ID As Integer
        Dim P As New clsMaximosMinimos

        For i = 0 To dgDatos.Rows.Count - 1
            dgDatos.CurrentCell = dgDatos.Rows(i).Cells(0)
            Application.DoEvents()

            IDProducto = BuscarIntDatoCitra("SELECT CIDPRODU01 FROM MGW10005 WHERE CCODIGOP01='" & dgDatos.Rows(i).Cells(0).Value & "'")
            cIDAlmacen = BuscarIntDatoCitra("SELECT CIDALMACEN FROM MGW10003 WHERE CCODIGOA01='" & dgDatos.Rows(i).Cells(1).Value & "'")
            Minimo = dgDatos.Rows(i).Cells(2).Value
            Maximo = dgDatos.Rows(i).Cells(3).Value
            Zona = ""
            Pasillo = ""
            Anaquel = ""
            Repisa = ""

            ID = BuscarIntDatoCitra("SELECT cIdAlmacen FROM MGW10016 WHERE cIdAlmacen=" & cIDAlmacen & " and cidprodu01=" & IDProducto)
            If ID = 0 Then
                Call SetSqlAdminPAQ("Update MGW10005 SET CBanubic01=1 where cidprodu01=" & IDProducto)
                P.cidAlmacen = cIDAlmacen
                P.CidProdu01 = IDProducto
                P.cExisten01 = Minimo
                P.cExisten02 = Maximo
                P.cZona = Zona
                P.cPasillo = Pasillo
                P.cAnaquel = Anaquel
                P.cRepisa = Repisa
                P.Insertar_Existencias_y_Ubicaciones()
            Else
                Call SetSqlAdminPAQ("UPDATE MGW10016 SET " & _
                            "cExisten01=" & Minimo & "," & _
                            "cExisten02=" & Maximo & " " & _
                            " WHERE cidAlmacen=" & cIDAlmacen & _
                            " AND cidprodu01=" & IDProducto)
            End If
        Next
        MsgBox("Los maximos y minimos se han guardado exitosamente", MsgBoxStyle.Information, "Aviso")
        dgDatos.Rows.Clear()
    End Sub
End Class